﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient
Public Class AlumnosDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"

    Public Function existeAlumno(ByVal pIdAlumno As Integer) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM alumnos " & _
                                            "WHERE IdAlumno = ?;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("IdAlumno", pIdAlumno)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function existeAlumnoCurp(ByVal pCurp As String) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM alumnos " & _
                                            "WHERE Curp = ?;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("Curp", pCurp)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function registraAlumno(ByVal pAlumno As AlumnosBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO alumnos(IdEmpresa, Nombre, Curp, Nivel, Status) " & _
                                            "VALUES (@IdEmpresa,@Nombre,@Curp,@Nivel,@Status);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@IdEmpresa", 0)
                    myComm.Parameters.AddWithValue("@Nombre", pAlumno.Nombre)
                    myComm.Parameters.AddWithValue("@Curp", pAlumno.Curp)
                    Select Case pAlumno.Nivel
                        Case AlumnosBE.EstatusNivel.Preescolar
                            myComm.Parameters.AddWithValue("@Nivel", "PRE")
                        Case AlumnosBE.EstatusNivel.Primaria
                            myComm.Parameters.AddWithValue("@Nivel", "PRI")
                        Case AlumnosBE.EstatusNivel.Secundaria
                            myComm.Parameters.AddWithValue("@Nivel", "SEC")
                        Case AlumnosBE.EstatusNivel.Profesional
                            myComm.Parameters.AddWithValue("@Nivel", "PRO")
                        Case AlumnosBE.EstatusNivel.Bachillerato
                            myComm.Parameters.AddWithValue("@Nivel", "BAC")
                    End Select
                    myComm.Parameters.AddWithValue("@Status", "A")
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaAlumno(ByVal pAlumno As AlumnosBE, ByRef msg As String) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT count(*) " &
                                            "FROM alumnos " & _
                                            "WHERE IdAlumno <> @id " &
                                                "AND  Curp = @descripcion;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@id", pAlumno.IdAlumno)
                    myComm.Parameters.AddWithValue("@descripcion", pAlumno.Curp)
                    myConn.Open()
                    If (Convert.ToInt16(myComm.ExecuteScalar()) > 0) Then
                        msg &= "Existe otro registro con la Curp especificado." & vbCrLf
                        Return False
                    End If

                    myComm.CommandText = "UPDATE alumnos " & _
                                            "SET Nombre = @Nombre, " & _
                                                "Curp = @Curp, " & _
                                                "Nivel = @Nivel, " & _
                                                "Status = @Status " & _
                                            "WHERE IdAlumno = @IdAlumno;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@Nombre", pAlumno.Nombre)
                    myComm.Parameters.AddWithValue("@Curp", pAlumno.Curp)
                    Select Case pAlumno.Nivel
                        Case AlumnosBE.EstatusNivel.Preescolar
                            myComm.Parameters.AddWithValue("@Nivel", "PRE")
                        Case AlumnosBE.EstatusNivel.Primaria
                            myComm.Parameters.AddWithValue("@Nivel", "PRI")
                        Case AlumnosBE.EstatusNivel.Secundaria
                            myComm.Parameters.AddWithValue("@Nivel", "SEC")
                        Case AlumnosBE.EstatusNivel.Profesional
                            myComm.Parameters.AddWithValue("@Nivel", "PRO")
                        Case AlumnosBE.EstatusNivel.Bachillerato
                            myComm.Parameters.AddWithValue("@Nivel", "BAC")
                    End Select
                    Select Case pAlumno.Status
                        Case AlumnosBE.EstatusAlumnos.Activo
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case AlumnosBE.EstatusAlumnos.Inactivo
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "A")
                    End Select
                    myComm.Parameters.AddWithValue("@IdAlumno", pAlumno.IdAlumno)
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function cambiaStatus(ByVal pId As Integer, ByVal pSetActivo As Boolean) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE alumnos " & _
                                            "SET Status = @Status " & _
                                            "WHERE IdAlumno = @IdAlumno;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    If (pSetActivo) Then
                        myComm.Parameters.AddWithValue("@Status", "A")
                    Else
                        myComm.Parameters.AddWithValue("@Status", "I")
                    End If
                    myComm.Parameters.AddWithValue("@IdAlumno", pId)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getAlumnoById(ByVal pIdAlumno As Integer) As AlumnosBE
        Dim tmpAlumno As AlumnosBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdEmpresa, IdAlumno, Nombre, Curp, Nivel, Status " & _
                                            "FROM alumnos " & _
                                            "WHERE IdAlumno = @IdAlumno;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@IdAlumno", pIdAlumno)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpAlumno = AlumnosFact.Build(myDR)
                        Else
                            tmpAlumno = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpAlumno

        Catch ex As Exception
            Throw
        Finally
            tmpAlumno = Nothing
        End Try
    End Function

    Public Function getAlumnoByCurp(ByVal pCurp As String) As AlumnosBE
        Dim tmpAlumno As AlumnosBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdEmpresa, IdAlumno, Nombre, Curp, Nivel, Status " & _
                                            "FROM alumnos " & _
                                            "WHERE Curp = @Curp;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@Curp", pCurp)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpAlumno = AlumnosFact.Build(myDR)
                        Else
                            tmpAlumno = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpAlumno

        Catch ex As Exception
            Throw
        Finally
            tmpAlumno = Nothing
        End Try
    End Function

    Public Function getlistadoAlumnos(ByVal pNombre As String, ByVal pCurp As String,
                                      ByVal pNivel As AlumnosBE.EstatusNivel, ByVal estatus As Enumeraciones.estatusParaBusquedas,
                                      ByVal pRegistroInicial As Integer, ByVal pPageSize As Integer,
                                      ByVal pSortColumn As Enumeraciones.campoOrdenamiento, ByVal pSortDirection As Enumeraciones.tipoOrdenamiento,
                                      ByRef pTotalRegistros As Integer) As List(Of AlumnosBE)
        pTotalRegistros = 0
        Dim AlumnosLs As List(Of AlumnosBE) = New List(Of AlumnosBE)
        Dim qryNivel As String = ""

        Select Case pNivel
            Case AlumnosBE.EstatusNivel.Preescolar
                qryNivel = " AND Nivel = 'PRE' "
            Case AlumnosBE.EstatusNivel.Primaria
                qryNivel = " AND Nivel = 'PRI' "
            Case AlumnosBE.EstatusNivel.Secundaria
                qryNivel = " AND Nivel = 'SEC' "
            Case AlumnosBE.EstatusNivel.Profesional
                qryNivel = " AND Nivel = 'PRO' "
            Case AlumnosBE.EstatusNivel.Bachillerato
                qryNivel = " AND Nivel = 'BAC' "
            Case AlumnosBE.EstatusNivel.Todos
                qryNivel = ""
        End Select

        Select Case estatus
            Case Enumeraciones.estatusParaBusquedas.Todos
                'No se aplica filtro
            Case Enumeraciones.estatusParaBusquedas.Activos
                qryNivel &= " AND Status = 'A' "
            Case Enumeraciones.estatusParaBusquedas.Inactivos
                qryNivel &= " AND Status = 'I' "
        End Select


        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT SQL_CALC_FOUND_ROWS IdEmpresa, IdAlumno, Nombre, Curp, Nivel, Status " & _
                                           "FROM alumnos " & _
                                           "WHERE Nombre LIKE @Nombre " & _
                                           "AND Curp LIKE @Curp " & _
                                           qryNivel &
                                           Enumeraciones.getOrderByStatement(Enumeraciones.catalogo.Alumnos, pSortColumn, pSortDirection) &
                                           " LIMIT @regInic, @pageSize;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@Nombre", "%" & pNombre & "%")
                    myComm.Parameters.AddWithValue("@Curp", "%" & pCurp & "%")
                    myComm.Parameters.AddWithValue("@regInic", pRegistroInicial)
                    myComm.Parameters.AddWithValue("@pageSize", pPageSize)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Alumnos As AlumnosBE = AlumnosFact.Build(myDR)
                            If (Not Alumnos Is Nothing) Then
                                AlumnosLs.Add(Alumnos)
                            End If
                            Alumnos = Nothing
                        End While
                    End Using
                    Using myComm2 As MySqlCommand = myConn.CreateCommand()
                        myComm2.CommandType = CommandType.Text
                        myComm2.CommandText = "SELECT FOUND_ROWS();"
                        pTotalRegistros = Convert.ToInt32(myComm2.ExecuteScalar())
                    End Using
                End Using
            End Using
            Return AlumnosLs
        Catch ex As Exception
            Throw
        Finally
            AlumnosLs = Nothing
            qryNivel = Nothing
        End Try
    End Function

#End Region

End Class
